﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace BangkokSteel
{
    public partial class frmSupplierProduct : Form, MainFormListener
    {

        private TabControl mainTab;
        private MySqlConnection conn = null;
        private MySqlDataAdapter adapter = null;
        private string MODE = "";
        private string ID = "";

        public frmSupplierProduct(TabControl mainTab)
        {
            this.mainTab = mainTab;
            InitializeComponent();
        }

        #region Form Event

        private void frmSupplierProduct_Load(object sender, EventArgs e)
        {
            conn = new MySqlConnection(BangkokSteel.Properties.Settings.Default.bangkoksteelConnectionString);

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();

            string strSQL = " SELECT * from supplier_product";
            adapter = new MySqlDataAdapter(strSQL, conn);
            adapter.Fill(dataSet1, "supplier_product");

            dataGridView1.DataSource = dataSet1.Tables["supplier_product"];
            this.dataGridView1.Columns["id"].Visible = false;

            //SET Combobox
            SetCbo();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.mainTab.TabPages.Remove(this.mainTab.SelectedTab);
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            EnableOrDisableMainPage(false);

            MODE = "ADD";

            ClearFields();

            panel1.Visible = true;
            cboSupplier.Focus();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0 && dataGridView1.SelectedRows.Count > 0)
            {
                EnableOrDisableMainPage(false);

                DataGridViewRow row = dataGridView1.SelectedRows[0];

                MODE = "EDIT";

                ID = row.Cells["ID"].Value.ToString();
                cboSupplier.SelectedValue = row.Cells["supplier_id"].Value.ToString();
                cboProduct.SelectedValue = row.Cells["product_id"].Value.ToString();
                txtPrice.Text = row.Cells["price"].Value.ToString();
                cboUnit.SelectedValue = row.Cells["unit_id"].Value.ToString();

                panel1.Visible = true;

                cboSupplier.Focus();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show("คุณต้องการลบข้อมูลนี้ใช่หรือไม่", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
            {
                DataGridViewRow row = dataGridView1.SelectedRows[0];

                string sql = "DELETE FROM `supplier_product` WHERE (`id` = @Original_id)";

                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@Original_id", MySqlDbType.String).Value = row.Cells["id"].Value;

                cmd.ExecuteNonQuery();

                MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว");
                MODE = "";

                // Refresh for New Data
                dataSet1.Tables["supplier_product"].Clear();
                adapter.Fill(dataSet1, "supplier_product");
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            try
            {
                if (Validation())
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }

                    conn.Open();

                    MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);

                    if ("ADD".Equals(MODE))
                    {
                        MySqlCommand cmd = (MySqlCommand)builder.GetInsertCommand(true);

                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@supplier_id", MySqlDbType.VarChar).Value = cboSupplier.SelectedValue;
                        cmd.Parameters.Add("@product_id", MySqlDbType.VarChar).Value = cboProduct.SelectedValue;
                        cmd.Parameters.Add("@price", MySqlDbType.Double).Value = txtPrice.Text;
                        cmd.Parameters.Add("@unit_id", MySqlDbType.Int32).Value = cboUnit.SelectedValue;
                      
                        int affectedRow = cmd.ExecuteNonQuery();
                        
                        MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว");
                        ClearFields();

                        cboSupplier.Focus();
                        cboSupplier.SelectAll();

                    }
                    else if ("EDIT".Equals(MODE))
                    {
                        string sql = @"UPDATE `supplier_product` SET `supplier_id` = @supplier_id, `product_id` = @product_id ";
                        sql = sql + " ,`price` = @price , `unit_id` = @unit_id ";
                        sql = sql + " WHERE (`id` = @Original_id)";

                        MySqlCommand cmd = new MySqlCommand(sql, conn);
                        cmd.Parameters.Add("@supplier_id", MySqlDbType.VarChar).Value = cboSupplier.SelectedValue;
                        cmd.Parameters.Add("@product_id", MySqlDbType.VarChar).Value = cboProduct.SelectedValue;
                        cmd.Parameters.Add("@price", MySqlDbType.Double).Value = txtPrice.Text;
                        cmd.Parameters.Add("@unit_id", MySqlDbType.Int32).Value = cboUnit.SelectedValue;
                        cmd.Parameters.Add("@Original_id", MySqlDbType.Int32).Value = ID;

                        cmd.ExecuteNonQuery();

                        MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว");

                        panel1.Visible = false;
                        EnableOrDisableMainPage(true);
                        ClearFields();

                        MODE = "";
                        ID = "";
                    }
                    conn.Close();
                    //// Refresh for New Data
                    dataSet1.Tables["supplier_product"].Clear();
                    adapter.Fill(dataSet1, "supplier_product");
                }
            }
            catch (FormatException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            EnableOrDisableMainPage(true);

            panel1.Visible = false;
            MODE = "";
            ClearFields();
        }

        private void cboProduct_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboProduct.Items.Count > 0)
            {
                DataSet ds = new DataSet();
                MySqlDataAdapter adap = new MySqlDataAdapter("SELECT * FROM unit WHERE id in (SELECT unit_id FROM product_unit WHERE product_id = '" + cboProduct.SelectedValue + "') ", conn);
                adap.Fill(ds, "unit");
                cboUnit.DataSource = ds.Tables["unit"];
                cboUnit.DisplayMember = "name";
                cboUnit.ValueMember = "id";
            }
        }

        #endregion

        #region Function

        private void EnableOrDisableMainPage(bool isEnable)
        {
            btnAdd.Enabled = isEnable;
            btnEdit.Enabled = isEnable;
            btnDelete.Enabled = isEnable;
            btnClose.Enabled = isEnable;
            dataGridView1.Enabled = isEnable;
        }

        private void ClearFields()
        {
            cboSupplier.SelectedIndex = 0;
            cboProduct.SelectedIndex = 0;
            txtPrice.Text = "";
            cboUnit.SelectedIndex = 0;
        }

        private void SetCbo()
        {
            //Supplier
            DataSet ds = new DataSet();

            MySqlDataAdapter adapter2 = new MySqlDataAdapter("SELECT * FROM supplier", conn);
            adapter2.Fill(ds, "supplier");
            cboSupplier.DataSource = ds.Tables["supplier"];
            cboSupplier.DisplayMember = "name";
            cboSupplier.ValueMember = "supplier_id";

            //Product
            MySqlDataAdapter adapter3 = new MySqlDataAdapter("SELECT * FROM product", conn);
            adapter3.Fill(ds, "product");
            cboProduct.DataSource = ds.Tables["product"];
            cboProduct.DisplayMember = "name";
            cboProduct.ValueMember = "id";

            //Product unit
            if (cboProduct.Items.Count > 0)
            { 
                MySqlDataAdapter adapter4 = new MySqlDataAdapter("SELECT * FROM unit WHERE id in (SELECT unit_id FROM product_unit WHERE product_id = '" + cboProduct.SelectedValue + "') ", conn);
                adapter4.Fill(ds, "unit");
                cboUnit.DataSource = ds.Tables["unit"];
                cboUnit.DisplayMember = "name";
                cboUnit.ValueMember = "id";
            }
            
        }

        private bool Validation()
        {

            if (cboSupplier.SelectedValue == null)
            {
                MessageBox.Show("กรุณาเลือก Supplier ให้เรียบร้อย");

                cboSupplier.Focus();
                cboSupplier.SelectAll();

                return false;
            }

            if (cboProduct.SelectedValue == null)
            {
                MessageBox.Show("กรุณาเลือก Product ให้เรียบร้อย");

                cboProduct.Focus();
                cboProduct.SelectAll();

                return false;
            }

            if (txtPrice.Text.Trim().Length == 0)
            {
                MessageBox.Show("กรุณาเลือกใส่ราคาให้เรียบร้อย");

                txtPrice.Focus();
                txtPrice.SelectAll();

                return false;
            }

            if (cboUnit.SelectedValue == null)
            {
                MessageBox.Show("กรุณาเลือก Unit ให้เรียบร้อย");

                cboUnit.Focus();
                cboUnit.SelectAll();

                return false;
            }

            //=========================================================================================================
            DataSet ds = new DataSet();
            conn = new MySqlConnection(BangkokSteel.Properties.Settings.Default.bangkoksteelConnectionString);

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            conn.Open();

            string sql = string.Format("SELECT * FROM supplier_product WHERE supplier_id = '{0}'",cboSupplier.SelectedValue);
            sql += string.Format(" AND product_id = '{0}' AND unit_id = {1}", cboProduct.SelectedValue,cboUnit.SelectedValue);
            MySqlDataAdapter adap = new MySqlDataAdapter(sql, conn);
            adap.Fill(ds);

            conn.Close();

            DataView dv = ds.Tables[0].DefaultView;

            if ("EDIT".Equals(MODE))
            {
                dv.RowFilter = string.Format("id <> {0}", ID);
            }
            
            if (!(dv.ToTable() == null || dv.ToTable().Rows.Count == 0))
            {
                MessageBox.Show("มีรหัสนี้อยู่ในระบบเรียบร้อย");
                return false;
            }
            //=========================================================================================================

            return true;
        }

        #endregion

        #region Interface

        void MainFormListener.onMainFormClose()
        {
            // TODO
        }

        public void onTabPageAdded()
        {
            // TODO
        }

        #endregion

        private void frmSupplierProduct_KeyDown(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.Escape:
                    {
                        if (panel1.Visible)
                        {
                            btnCancel_Click(null, null);
                        }
                        else
                        {
                            btnClose_Click(null, null);
                        }
                    }
                    break;
                case Keys.F2:
                    {
                        if (panel1.Visible)
                        {
                            btnOK_Click(null, null);
                        }
                        else
                        {
                            btnAdd_Click(null, null);
                        }
                    }
                    break;

                case Keys.F3:
                    {
                        //if (panel1.Visible)
                        //{
                        //    btnOK_Click(null, null);
                        //}
                        //else
                        //{
                        //    btnEdit_Click(null, null);
                        //}
                        btnEdit_Click(null, null);

                    }
                    break;

                case Keys.F4:
                    {
                        btnDelete_Click(null, null);
                    }
                    break;
            }
        }
    }
}
